from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Enum
from sqlalchemy.orm import relationship
from datetime import datetime
from sqlalchemy import Table

db = SQLAlchemy()

# Association table for the many-to-many relationship between BoxTag and Image
box_tags_images = Table(
    'box_tags_images', db.metadata,
    Column('box_tag_id', Integer, ForeignKey('box_tags.id', ondelete='CASCADE'), primary_key=True),
    Column('image_id', Integer, ForeignKey('images.id', ondelete='CASCADE'), primary_key=True),
    Column('createTime', DateTime, nullable=False, default=datetime.now),
    Column('updateTime', DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
)

# Association table for the many-to-many relationship between Configuration and Image
ConfigurationsImages = Table(
    'configurations_images', db.metadata,
    Column('configuration_id', Integer, ForeignKey('configurations.id', ondelete='CASCADE'), primary_key=True),
    Column('image_id', Integer, ForeignKey('images.id', ondelete='CASCADE'), primary_key=True),
    Column('createTime', DateTime, nullable=False, default=datetime.now),
    Column('updateTime', DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
)

# Association table for the many-to-many relationship between BoxType and Image
box_types_images = Table(
    'box_types_images', db.metadata,
    Column('box_type_id', Integer, ForeignKey('box_types.id', ondelete='CASCADE'),
           primary_key=True),
    Column('image_id', Integer, ForeignKey('images.id', ondelete='CASCADE'), primary_key=True),
    Column('createTime', DateTime, nullable=False, default=datetime.now),
    Column('updateTime', DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
)

# 定义中间关系表
field_area_images = Table(
    'field_area_images', db.Model.metadata,
    Column('field_area_id', Integer, ForeignKey('field_area.id', ondelete='CASCADE'), primary_key=True),
    Column('image_id', Integer, ForeignKey('images.id', ondelete='CASCADE'), primary_key=True),
    Column('createTime', DateTime, nullable=False, default=datetime.now),
    Column('updateTime', DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
)


leads_ips = db.Table(
    'leads_ips',
    db.Column('lead_id', db.Integer, db.ForeignKey('leads.id', ondelete='CASCADE'), primary_key=True),
    db.Column('ip_id', db.Integer, db.ForeignKey('ips.id', ondelete='CASCADE'), primary_key=True),
    db.Column('createTime', db.DateTime, default=datetime.utcnow, comment='创建时间'),
    db.Column(
        'updateTime', db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='最后一次的更新时间')
)


class Permission(db.Model):
    """
    权限模型
    """
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    code = Column(String(50), nullable=False, unique=True)
    type = Column(String(255), nullable=False)
    parentId = Column(Integer, ForeignKey('permission.id'))
    path = Column(String(255))
    redirect = Column(String(255))
    icon = Column(String(255))
    component = Column(String(255))
    layout = Column(String(255))
    keepAlive = Column(Integer)
    method = Column(String(255))
    description = Column(String(255))
    show = Column(Integer, nullable=False, default=1)
    enable = Column(Integer, nullable=False, default=1)
    order = Column(Integer)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    children = relationship('Permission', backref=db.backref('parent', remote_side=[id]))


class Profile(db.Model):
    """
    用户资料模型
    """
    id = Column(Integer, primary_key=True, autoincrement=True)
    gender = Column(Integer)
    avatar = Column(
        String(255), nullable=False,
        default='https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif?imageView2/1/w/80/h/80')
    address = Column(String(255))
    email = Column(String(255))
    userId = Column(Integer, ForeignKey('user.id'), nullable=False, unique=True)
    nickName = Column(String(10))
    user = relationship('User', backref='profile')
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)


class Role(db.Model):
    """
    角色模型
    """
    id = Column(Integer, primary_key=True, autoincrement=True)
    code = Column(String(50), nullable=False, unique=True)
    name = Column(String(50), nullable=False, unique=True)
    enable = Column(Integer, nullable=False, default=1)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    permissions = relationship('Permission', secondary='role_permissions_permission', backref='roles')


class RolePermissionsPermission(db.Model):
    """
    角色权限关联模型
    """
    __tablename__ = 'role_permissions_permission'
    roleId = Column(Integer, ForeignKey('role.id'), primary_key=True)
    permissionId = Column(Integer, ForeignKey('permission.id'), primary_key=True)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)


class User(db.Model):
    """
    用户模型
    """
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False, unique=True)
    password = Column(String(255), nullable=False)
    enable = Column(Integer, nullable=False, default=1)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    roles = relationship('Role', secondary='user_roles_role', backref='users')


class UserRolesRole(db.Model):
    """
    用户角色关联模型
    """
    __tablename__ = 'user_roles_role'
    userId = Column(Integer, ForeignKey('user.id'), primary_key=True)
    roleId = Column(Integer, ForeignKey('role.id'), primary_key=True)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)


class UserBehavior(db.Model):
    """
    用户行为模型
    """
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, nullable=False)
    behavior_time = db.Column(db.DateTime, nullable=False)
    detail = db.Column(db.Text, nullable=False)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return f'<UserBehavior {self.user_id}>'


class TokenRecord(db.Model):
    """
    令牌记录模型
    """
    __tablename__ = 'token_records'

    id = db.Column(db.Integer, primary_key=True)
    token = db.Column(db.String(256), unique=True, nullable=False)
    user_id = db.Column(db.Integer, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    remarks = db.Column(db.Text)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return f"<TokenRecord id={self.id} token={self.token}>"


class Contact(db.Model):
    __tablename__ = 'contacts'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(256), nullable=False)
    phone = db.Column(db.String(128), nullable=False)
    address = db.Column(db.String(256))
    qq = db.Column(db.String(128))
    email = db.Column(db.String(256))
    remark = db.Column(db.Text)
    createTime = db.Column(db.DateTime, default=datetime.utcnow)
    updateTime = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    images = db.relationship('Image', secondary='contact_images', backref=db.backref('contacts', lazy='dynamic'))
    boxs = db.relationship('Box', backref='contact', lazy=True)
    __table_args__ = (
        db.UniqueConstraint('name', 'phone', name='unique_name_phone'),
    )


class FieldStack(db.Model):
    __tablename__ = 'field_stack'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(256), nullable=False)
    phone = db.Column(db.String(256))
    address = db.Column(db.String(256), nullable=False)
    website = db.Column(db.String(256))
    remark = db.Column(db.Text)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    contacts = db.relationship('Contact', secondary='field_stack_contacts_contact', backref='fields')
    images = db.relationship('Image', secondary='field_stack_images',
                             backref=db.backref('field_stacks', lazy='dynamic'))

    __table_args__ = (
        db.UniqueConstraint('name', 'address', name='unique_name_address'),
    )


class FieldStackContact(db.Model):
    __tablename__ = 'field_stack_contacts_contact'

    fieldStackId = db.Column(db.Integer, db.ForeignKey('field_stack.id'), primary_key=True)
    contactId = db.Column(db.Integer, db.ForeignKey('contacts.id'), primary_key=True)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)


class FieldArea(db.Model):
    __tablename__ = 'field_area'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(256), nullable=False)
    location = Column(
        Enum('场内', '场外', name='location'), nullable=False, default='场内',
        comment='场内外')
    container_length = Column(
        Enum('20英尺', '40英尺', '45英尺', '错误长度', '不区分长度', name='container_length'), nullable=False,
        default='20英尺', comment='箱体长度')
    max_capacity = Column(Integer, nullable=False, default=50, comment='最大箱数')
    remark = db.Column(db.Text)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    field_stack_id = db.Column(db.Integer, db.ForeignKey('field_stack.id'), nullable=False)

    field_stack = db.relationship('FieldStack', backref=db.backref('areas', cascade='all, delete-orphan'))
    images = db.relationship('Image', secondary=field_area_images, backref=db.backref('field_areas', lazy='dynamic'))
    boxs = db.relationship('Box', backref='area', lazy=True)

    __table_args__ = (
        db.UniqueConstraint('name', 'field_stack_id', 'container_length', name='unique_name_field_stack_id'),
    )


class BoxType(db.Model):
    __tablename__ = 'box_types'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    length = Column(Enum('20英尺', '40英尺', '45英尺', '错误长度', '不区分长度'), nullable=False, comment='集装箱长度')
    box_type = Column(String(255), nullable=False, comment='箱型')
    box_code = Column(String(255), nullable=False, comment='箱型代码')
    typeName = Column(String(255), unique=True, nullable=False, comment='95码')
    full_name = Column(String(255), nullable=False, comment='英文全称')
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    remark = db.Column(db.Text)

    boxs = db.relationship('Box', backref='box_type', lazy=True)
    images = db.relationship('Image', secondary=box_types_images, backref=db.backref('box_types', lazy='dynamic'))

    def __init__(self, type_name, remark=""):
        self.typeName = type_name
        self.remark = remark


class BoxTag(db.Model):
    __tablename__ = 'box_tags'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    tagName = db.Column(db.String(256), unique=True, nullable=False)
    remark = db.Column(db.Text)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)

    boxs = db.relationship('Box', secondary='boxs_tags', backref=db.backref('tags', lazy='dynamic'))
    images = db.relationship('Image', secondary=box_tags_images, backref=db.backref('box_tags', lazy='dynamic'))

    def __init__(self, tag_name, remark=""):
        self.tagName = tag_name
        self.remark = remark


class Image(db.Model):
    __tablename__ = 'images'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    imageUrl = db.Column(db.String(256), unique=True, nullable=False)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    remark = db.Column(db.Text)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
    boxs = db.relationship('Box', secondary='boxs_images', backref=db.backref('images', lazy='select'))

    def __init__(self, image_url):
        self.imageUrl = image_url


class Box(db.Model):
    __tablename__ = 'boxs'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    boxId = db.Column(db.String(256), unique=True, nullable=False)
    boxType_id = db.Column(db.Integer, db.ForeignKey('box_types.id'), nullable=True)
    contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id'), nullable=True)
    area_id = db.Column(db.Integer, db.ForeignKey('field_area.id'), nullable=True)
    remark = db.Column(db.Text)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)

    def __init__(self, box_id, box_type_id, contact_id, area_id, remark=None):
        self.boxId = box_id
        self.boxType_id = box_type_id
        self.contact_id = contact_id
        self.area_id = area_id
        self.remark = remark


class BoxImage(db.Model):
    __tablename__ = 'boxs_images'
    box_id = db.Column(db.Integer, db.ForeignKey('boxs.id'), primary_key=True)
    image_id = db.Column(db.Integer, db.ForeignKey('images.id'), primary_key=True)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)


class BoxTagRelation(db.Model):
    __tablename__ = 'boxs_tags'
    box_id = db.Column(db.Integer, db.ForeignKey('boxs.id'), primary_key=True)
    tag_id = db.Column(db.Integer, db.ForeignKey('box_tags.id'), primary_key=True)
    createTime = Column(DateTime, nullable=False, default=datetime.now)
    updateTime = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)


class ContactImage(db.Model):
    __tablename__ = 'contact_images'

    contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id'), primary_key=True)
    image_id = db.Column(db.Integer, db.ForeignKey('images.id'), primary_key=True)


class FieldStackImage(db.Model):
    __tablename__ = 'field_stack_images'

    field_stack_id = db.Column(db.Integer, db.ForeignKey('field_stack.id'), primary_key=True)
    image_id = db.Column(db.Integer, db.ForeignKey('images.id'), primary_key=True)


class Configuration(db.Model):
    __tablename__ = 'configurations'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=False, unique=True)
    value = db.Column(db.Text)
    remark = db.Column(db.Text)
    createTime = db.Column(db.DateTime, default=datetime.utcnow)
    updateTime = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    images = db.relationship(
        'Image', secondary=ConfigurationsImages, backref=db.backref('configurations', lazy='dynamic'))


class Lead(db.Model):
    __tablename__ = 'leads'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='表索引ID')
    from_source = db.Column(db.String(255), comment='来源')
    ppn = db.Column(db.String(255), comment='纯电话号码')
    pn = db.Column(db.String(255), unique=True, nullable=False, comment='带国家码的电话号码')
    cc = db.Column(db.String(10), comment='国家码')
    pi = db.Column(db.Text, comment='电话号码信息')
    times = db.Column(db.Integer, default=0, nullable=False, comment='号码访问次数')
    remark = db.Column(db.Text, comment='备注')
    createTime = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    updateTime = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='最后一次的更新时间')
    ips = db.relationship('Ip', secondary=leads_ips, backref=db.backref('leads', lazy='dynamic'))


class Ip(db.Model):
    __tablename__ = 'ips'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='表索引ID')
    from_source = db.Column(db.String(255), comment='来源')
    ip = db.Column(db.String(45), unique=True, nullable=False, comment='IP地址')
    country = db.Column(db.String(100), comment='国家')
    province = db.Column(db.String(100), comment='省、州')
    city = db.Column(db.String(100), comment='城市')
    li = db.Column(db.Text, comment='IP位置信息')
    times = db.Column(db.Integer, nullable=False, comment='地址出现次数')
    remark = db.Column(db.Text, comment='备注')
    createTime = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    updateTime = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='最后一次的更新时间')
